from fastapi import APIRouter, Query, Depends
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

good_inv_router = APIRouter(prefix="/goodInv", tags=["耗材库存管理"], dependencies=[Depends(oauth2_scheme)])


class InvSearch(PaginationBase):
    fsiteNo: str = Field(default=None, description="站点编号")
    fmonth: str = Field(default=None, description="月份")
    fstockno: str = Field(default=None, description="仓库编号")
    fgoodname: str = Field(default=None, description="耗材名称")


@good_inv_router.get("/list", summary="耗材库存查询")
def list_good_inv(inv: InvSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.m_tc_good_inv a,lims.m_ts_material_info b,lims.i_ts_stock c"
    sql = sql + " where 1=1"
    sql = sql + " and a.fsite_no=b.fsite_no and a.fmaterialno=b.fmaterialno"
    sql = sql + " and a.fsite_no=c.fsite_no and a.fstockno=c.fstockno"
    if inv.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :site_no "
        param["site_no"] = inv.fsiteNo
    if inv.fmonth is not None:
        sql = sql + " and a.fmonth = :fmonth "
        param["fmonth"] = inv.fmonth
    if inv.fstockno is not None:
        sql = sql + " and a.fstockno = :fstockno "
        param["fstockno"] = inv.fstockno
    if inv.fgoodname is not None:
        sql = sql + " and b.fgoodname like concat('%', :fgoodname ,'%') "
        param["fgoodname"] = inv.fgoodname
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fmonth,c.fstockname,b.fmaterialno,b.fmaterialname,b.fspec,b.fordunit"
    sql = sql + ",a.fbegqty,a.frecqty,a.fretqty,a.fmovqty,a.foutqty,finqty"
    sql = sql + ",a.fuseqty,a.fbalqty,a.fbalqty*b.fordpack repertory,a.fstockno"
    sql = sql + " from lims.m_tc_good_inv a,lims.m_ts_material_info b,lims.i_ts_stock c"
    sql = sql + " where 1=1"
    sql = sql + " and a.fsite_no=b.fsite_no and a.fmaterialno=b.fmaterialno"
    sql = sql + " and a.fsite_no=c.fsite_no and a.fstockno=c.fstockno"
    if inv.fsiteNo is not None:
        sql = sql + " and a.fsite_no = :site_no "
        param["site_no"] = inv.fsiteNo
    if inv.fmonth is not None:
        sql = sql + " and a.fmonth = :fmonth "
        param["fmonth"] = inv.fmonth
    if inv.fstockno is not None:
        sql = sql + " and a.fstockno = :fstockno "
        param["fstockno"] = inv.fstockno
    if inv.fgoodname is not None:
        sql = sql + " and b.fgoodname like concat('%', :fgoodname ,'%') "
        param["fgoodname"] = inv.fgoodname
    sql = sql + " order by  a.fmonth,c.fstockname,b.fmaterialno"
    sql = sql + " limit :limit offset :offset "
    param["offset"] = inv.offset
    param["limit"] = inv.limit
    data = db.execute(text(sql), param).fetchall()

    info_dict = {}
    info_dict['total'] = count
    info_dict['pages'] = (count + inv.limit - 1) // inv.limit
    info_dict['limit'] = inv.limit
    info_dict['page'] = inv.pageIndex

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fmonth'] = data[key][0]
        d1['fstockname'] = data[key][1]
        d1['fmaterialno'] = data[key][2]
        d1['fmaterialname'] = data[key][3]
        d1['fspec'] = data[key][4]
        d1['fordunit'] = data[key][5]
        d1['fbegqty'] = float(data[key][6])
        d1['frecqty'] = float(data[key][7])
        d1['fretqty'] = float(data[key][8])
        d1['fmovqty'] = float(data[key][9])
        d1['foutqty'] = float(data[key][10])
        d1['finqty'] = float(data[key][11])
        d1['fuseqty'] = float(data[key][12])
        d1['fbalqty'] = float(data[key][13])
        d1['repertory'] = float(data[key][14])
        d1['fstock'] = data[key][15]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": inv.limit, "page": inv.pageIndex,
                 "pages": (count + inv.limit - 1) // inv.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
